﻿USE [TanNamChinh]
GO
/****** Object:  StoredProcedure [dbo].[GetBuyingRateOfFileImport]    Script Date: 12/25/2013 16:45:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetBuyingRateOfFileImport]
@id_file AS INT,
@id_voyage AS INT,
@id_mbl AS INT,
@id_filetype AS INT	
AS
BEGIN

--Cách tính
--Đối với hàng nhập, đã được kiểm tra đây là hàng lẻ thì mới sử dụng store này
--Phần Quantity phụ thuộc vào số CBM
--Lấy số CBM của cont
Declare @CBM  as float
Set @CBM = 0.0
select @CBM = tc.CBM 
from  tblMBL tb,tblFileMBL tfm, tblCont tc, tblFreightTariff tft,
			tblContType tct, tblLineCont tl
			WHERE  
			(tb.ID_Voyage = @id_voyage
			AND tfm.ID_File=@id_file
			AND tc.ID_File = @id_file
			AND tb.ID_MBL = @id_mbl
			AND tfm.ID_MBL = @id_mbl
			AND tc.ID_LineCont = tl.ID_LineCont
			AND tl.ID_ContType = tct.ID_ContType
			AND tft.ID_ContType=tl.ID_ContType
			AND tft.ID_Voyage=@id_voyage
			AND tft.ID_Fee IN (SELECT tf.ID_Fee
		                     FROM tblFee tf WHERE tf.ID_FeeType = 1 and tf.IsBill=0)
		    AND tft.IsBuying = 1)
		    
--Kiểm tra giá trị CBM
IF	(0< @CBM) and (@CBM <=1)
BEGIN   
	set @CBM = 1
--Price = UnitPrice * 1
	 	--Trả về kết quả với phần số lượng cont, Phần đơn giá giữ nguyên không tính tổng
	  ----------------------
			( SELECT tf.ID_Fee, tf.FeeName,Quantity = @CBM, UnitPrice =  TempTable.Price ,SUM(TempTable.Price) AS Price,InvoiceNo='', Ghichu='', TempTable.ID_Currency
			FROM
			(
			SELECT tc.ID_Cont,tl.ID_ContType,tft.ID_Fee,tft.Price,tft.ID_Currency
			FROM tblMBL tb,tblFileMBL tfm, tblCont tc, tblFreightTariff tft,
			tblContType tct, tblLineCont tl
			WHERE  
			tb.ID_Voyage = @id_voyage
			AND tfm.ID_File=@id_file
			AND tc.ID_File = @id_file
			AND tb.ID_MBL = @id_mbl
			AND tfm.ID_MBL = @id_mbl
			AND tc.ID_LineCont = tl.ID_LineCont
			AND tl.ID_ContType = tct.ID_ContType
			AND tft.ID_ContType=tl.ID_ContType
			AND tft.ID_Voyage=@id_voyage
			AND tft.ID_Fee IN (SELECT tf.ID_Fee
		                     FROM tblFee tf WHERE tf.ID_FeeType = 1 and tf.IsBill=0)
		    AND tft.IsBuying = 1
			)  AS TempTable JOIN tblFee tf ON tf.ID_Fee = TempTable.ID_Fee
			GROUP BY   tf.ID_Fee, tf.FeeName,TempTable.ID_Currency,TempTable.Price)
			
			 UNION
			 (
			 SELECT tf.ID_Fee,tf.FeeName, Quantity = @CBM, UnitPrice =tft.Price ,tft.Price,InvoiceNo='', Ghichu='', tft.ID_Currency
				FROM tblMBL tb,tblFileMBL tfm, tblVoyageFee tft, tblFee tf
				WHERE  
				tb.ID_Voyage = @id_voyage
				AND tfm.ID_File=@id_file
				AND tb.ID_MBL = @id_mbl
				AND tfm.ID_MBL = @id_mbl
				AND tft.ID_Voyage=@id_voyage
				AND tft.ID_Fee  IN (SELECT tf.ID_Fee
								 FROM tblFee tf WHERE tf.ID_FeeType = 1 and tf.IsBill=1)
				 AND tft.IsBuying = 1
				and tft.ID_Fee = tf.ID_Fee)
 END
 IF @CBM >1
 BEGIN
 --Nếu mà lớn hơn 1
 	( SELECT tf.ID_Fee, tf.FeeName,Quantity = @CBM, UnitPrice = TempTable.Price ,(SUM(TempTable.Price)*@CBM) AS Price,InvoiceNo='', Ghichu='', TempTable.ID_Currency
			FROM
			(
			SELECT tc.ID_Cont,tl.ID_ContType,tft.ID_Fee,tft.Price,tft.ID_Currency
			FROM tblMBL tb,tblFileMBL tfm, tblCont tc, tblFreightTariff tft,
			tblContType tct, tblLineCont tl
			WHERE  
			tb.ID_Voyage = @id_voyage
			AND tfm.ID_File=@id_file
			AND tc.ID_File = @id_file
			AND tb.ID_MBL = @id_mbl
			AND tfm.ID_MBL = @id_mbl
			AND tc.ID_LineCont = tl.ID_LineCont
			AND tl.ID_ContType = tct.ID_ContType
			AND tft.ID_ContType=tl.ID_ContType
			AND tft.ID_Voyage=@id_voyage
			AND tft.ID_Fee IN (SELECT tf.ID_Fee
		                     FROM tblFee tf WHERE tf.ID_FeeType = 1 and tf.IsBill=0)
		    AND tft.IsBuying = 1
			)  AS TempTable JOIN tblFee tf ON tf.ID_Fee = TempTable.ID_Fee
			GROUP BY   tf.ID_Fee, tf.FeeName,TempTable.ID_Currency,TempTable.Price)
			
			 UNION
			 (			SELECT tf.ID_Fee,tf.FeeName, Quantity = 1, UnitPrice =tft.Price ,tft.Price,InvoiceNo='', Ghichu='', tft.ID_Currency
				FROM tblMBL tb,tblFileMBL tfm, tblVoyageFee tft, tblFee tf
				WHERE  
				tb.ID_Voyage = @id_voyage
				AND tfm.ID_File=@id_file
				AND tb.ID_MBL = @id_mbl
				AND tfm.ID_MBL = @id_mbl
				AND tft.ID_Voyage=@id_voyage
				AND tft.ID_Fee  IN (SELECT tf.ID_Fee
								 FROM tblFee tf WHERE tf.ID_FeeType = 1 and tf.IsBill=1)
				 AND tft.IsBuying = 1
				and tft.ID_Fee = tf.ID_Fee)
	END
	 
END